Imports System.Data.OracleClient
Namespace persistent
    Public Class GhhTransfer
        Public Shared Function getActiveSourceCoProduct(ByVal sourceCoId As Integer, ByVal destCoId As Integer, ByRef result As String) As DataTable
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.pkg_ghh_transfer.getActiveCoTransProduct")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)


            con.Open()
            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("p_source_co_id", OracleType.Int32).Value = sourceCoId
            cmd.Parameters.Add("p_dest_co_id", OracleType.Int32).Value = destCoId
            cmd.Parameters.Add("p_result", OracleType.NVarChar, 1000).Direction = ParameterDirection.Output
            cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

            cmd.ExecuteNonQuery()

            result = cmd.Parameters("p_result").Value

            If result <> "OK" Then
                con.Close()
                cmd.Dispose()
                da.Dispose()
                Return New DataTable
            End If

            Dim dt As New DataTable

            dt.Columns.Add("sncode")
            dt.Columns.Add("product_des")
            dt.Columns.Add("service_des")
            dt.Columns.Add("entry_date")
            dt.Columns.Add("state")
            dt.Columns.Add("flag")
            dt.Columns.Add("dest_subscription_id")
            dt.Columns.Add("source_subscription_id")
            dt.Columns.Add("operation_type")

            Dim rdr As OracleDataReader
            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            Dim str(8) As String

            While rdr.Read
                str(0) = rdr.Item(0)
                str(1) = rdr.Item(1)
                str(2) = rdr.Item(2)
                str(3) = rdr.Item(3)
                str(4) = rdr.Item(4)
                str(5) = rdr.Item(5)
                str(6) = rdr.Item(6)
                str(7) = rdr.Item(7)
                str(8) = rdr.Item(8)

                dt.Rows.Add(str)
            End While
            con.Close()

            Return dt

        End Function
        Public Shared Function getSoruceCoProduct(ByVal sourceCoId As Integer, ByVal destCoId As Integer, ByRef result As String) As DataTable

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.pkg_ghh_transfer.get_co_trans_product")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)

            con.Open()
            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("p_source_co_id", OracleType.Int32).Value = sourceCoId
            cmd.Parameters.Add("p_dest_co_id", OracleType.Int32).Value = destCoId
            cmd.Parameters.Add("p_result", OracleType.NVarChar, 1000).Direction = ParameterDirection.Output
            cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

            cmd.ExecuteNonQuery()

            result = cmd.Parameters("p_result").Value

            If result <> "OK" Then
                con.Close()
                cmd.Dispose()
                da.Dispose()
                Return New DataTable
            End If

            Dim dt As New DataTable

            dt.Columns.Add("sncode")
            dt.Columns.Add("product_des")
            dt.Columns.Add("service_des")
            dt.Columns.Add("entry_date")
            dt.Columns.Add("state")
            dt.Columns.Add("flag")
            dt.Columns.Add("dest_subscription_id")
            dt.Columns.Add("source_subscription_id")
            dt.Columns.Add("operation_type")

            Dim rdr As OracleDataReader
            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            Dim str(8) As String

            While rdr.Read
                str(0) = rdr.Item(0)
                str(1) = rdr.Item(1)
                str(2) = rdr.Item(2)
                str(3) = rdr.Item(3)
                str(4) = rdr.Item(4)
                str(5) = rdr.Item(5)
                str(6) = rdr.Item(6)
                str(7) = rdr.Item(7)
                str(8) = rdr.Item(8)

                dt.Rows.Add(str)
            End While

            con.Close()
            Return dt

        End Function

        Public Shared Sub transferServices(ByVal userId As String, ByVal source_subscription_id As Integer, ByVal dest_subscription_id As Integer, ByVal sncode As Integer, ByVal operation_type As String, ByRef result As String)
            result = String.Empty

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            con.Open()
            Dim cmd As New OracleCommand
            cmd.Connection = con
            cmd.CommandText = "eu_bill.pkg_ghh_transfer.transfer_services"
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("source_subscription_id", OracleType.Int32).Value = source_subscription_id
            cmd.Parameters.Add("dest_subscription_id", OracleType.Int32).Value = dest_subscription_id
            cmd.Parameters.Add("p_sncode", OracleType.Int32).Value = sncode
            cmd.Parameters.Add("p_user_id", OracleType.VarChar).Value = userId
            cmd.Parameters.Add("p_operation_type", OracleType.VarChar).Value = operation_type
            cmd.Parameters.Add("p_result", OracleType.NVarChar, 1000).Direction = ParameterDirection.Output

            cmd.ExecuteNonQuery()

            result = cmd.Parameters("p_result").Value

            con.Close()

        End Sub
    End Class
End Namespace
